package com.yict.csms.resourceplan.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.stereotype.Repository;

import com.yict.csms.resourceplan.dao.INGenDataDao;
import com.yict.csms.resourceplan.entity.NGenData;

@Repository("nGenDataDao")
public class NGenDataDaoImpl implements INGenDataDao {
	private DataSource nGends;

	@Override
	public NGenData findNGenData(String vesselCode, String voyageCode)
			throws Exception {
		NGenData vo = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select t1.VOYID_VESSEL_CODE, t1.Voyid_Voyage_Code, t2.Vessel_Type, t1.Voyid_Company_Code, t1.Quay_Crane_List, t1.Berth_Id, case t1.berthing_datetime_status when 'F' then null else t1.Berthing_Datetime end ETB, case t1.berthing_datetime_status when 'F' then t1.Berthing_Datetime else null end ATB, case t1.departure_datetime_status when 'F' then t1.Departure_Datetime else null end ATD, t3.Ld_Ds_Operation_Complete_Dt TCC, case t1.departure_datetime_status when 'F' then null else t1.Departure_Datetime end ETD, t1.Departure_Datetime_Status ETD_status, ");
		sql.append("sum(case when t4.Shipment_Instruction_Type = 'PL' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' then 1 else 0 end) DS, ");
		sql.append("sum(case when t4.Shipment_Instruction_Type = 'PL' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 1 else 0 end) DSI, ");
		sql.append("sum(case when t4.Shipment_Instruction_Type = 'PL' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 0 else 1 end) DSO, ");
		sql.append("sum(case when t4.Shipment_Instruction_Type = 'PS' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 1 else 0 end) LDI, ");
		sql.append("sum(case when t4.Shipment_Instruction_Type = 'PS' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 1 else 0 end) LDO, ");
		sql.append("sum(case when t4.Shipment_Instruction_Type in ('HL', 'HS') and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' then 1 else 0 end) RS, ");
		sql.append("sum(case when t4.Shipment_Instruction_Type in ('RL', 'RS') and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' then 0 else 1 end) SH ");
		sql.append("from VOYAGE t1 left join VOY_VESSEL_MODEL t2 on t1.VOYID_COMPANY_CODE = t2.Voyid_Company_Code and t1.VOYID_VESSEL_CODE = t2.Voyid_Vessel_Code and t1.VOYID_VOYAGE_CODE = t2.Voyid_Voyage_Code ");
		sql.append("left join VOYAGE_OPERATIONAL_CONTROL t3 on t1.VOYID_COMPANY_CODE = t3.Voyid_Company_Code and t1.VOYID_VESSEL_CODE = t3.Voyid_Vessel_Code and t1.VOYID_VOYAGE_CODE = t3.Voyid_Voyage_Code ");
		sql.append("left join SHIPMENT_INSTRUCTION t4 on t1.VOYID_COMPANY_CODE = t4.Voyid_Company_Code and t1.VOYID_VESSEL_CODE = t4.Voyid_Vessel_Code and t1.VOYID_VOYAGE_CODE = t4.Voyid_Voyage_Code ");
		sql.append("left join cwp_job_bay_coverage t5 on t1.VOYID_COMPANY_CODE = t5.Voyid_Company_Code and t1.VOYID_VESSEL_CODE = t5.Voyid_Vessel_Code and t1.VOYID_VOYAGE_CODE = t5.Voyid_Voyage_Code ");
		sql.append("left join SHIPMENT t6 on t4.cntr_id = t6.cntr_id and t4.shipment_id = t6.shipment_id ");
		sql.append("where t1.Voyid_Voyage_Code = ? and t1.VOYID_VESSEL_CODE = :vesselCode and t1.Departure_Datetime_Status = ? ");
		sql.append("group by t1.VOYID_VESSEL_CODE, t1.Voyid_Voyage_Code, t2.Vessel_Type, t1.Voyid_Company_Code, t1.Quay_Crane_List, t1.Berth_Id, t1.Berthing_Datetime, t1.Berthing_Datetime, t1.Departure_Datetime, t3.Ld_Ds_Operation_Complete_Dt, t1.Departure_Datetime, t1.Departure_Datetime_Status,t1.berthing_datetime_status");
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = nGends.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, voyageCode);
			pstmt.setString(2, vesselCode);
			pstmt.setString(3, "F");
			rs = pstmt.executeQuery();
			List<NGenData> list = this.rs2vo(rs);
			if(list.size() > 0) vo = list.get(0);
		} finally{
			if(rs != null) rs.close();
			if(pstmt != null) pstmt.close();
			if(conn != null) conn.close();
		}
		return vo;
	}
	
	private List<NGenData> rs2vo(ResultSet rs) throws Exception{
		List<NGenData> list = new ArrayList<NGenData>();
		while(rs.next()){
			NGenData vo = new NGenData();
			vo.setVesselCode(rs.getString("VOYID_VESSEL_CODE"));
			vo.setVoyageCode(rs.getString("VOYID_VOYAGE_CODE"));
			vo.setVesselType(rs.getString("VESSEL_TYPE"));
			vo.setCompanyCode(rs.getString("VOYID_COMPANY_CODE"));
			vo.setQuayCraneList(rs.getString("QUAY_CRANE_LIST"));
			vo.setBerthId(rs.getString("BERTH_ID"));
			vo.setEtb(rs.getDate("ETB"));
			vo.setAtb(rs.getDate("ATB"));
			vo.setAtd(rs.getDate("ATD"));
			vo.setTcc(rs.getDate("TCC"));
			vo.setEtd(rs.getDate("ETD"));
			vo.setEtdStutas(rs.getString("ETD_STATUS"));
			vo.setDs(rs.getInt("DS"));
			vo.setLd(rs.getInt("LD"));
			vo.setDsI(rs.getInt("DSI"));
			vo.setDsO(rs.getInt("DSO"));
			vo.setLdI(rs.getInt("LDI"));
			vo.setLdO(rs.getInt("LDO"));
			vo.setRs(rs.getInt("RS"));
			vo.setSh(rs.getInt("SH"));
			list.add(vo);
		}
		return list;
	}

	public DataSource getnGends() {
		return nGends;
	}

	@Resource(name = "nGenDataSource")
	public void setnGends(DataSource nGends) {
		this.nGends = nGends;
	}
}
